﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using SqlCeManager.Tools;
using DevExpress.XtraEditors.Repository;
using System.Data.SqlServerCe;
using DevExpress.XtraGrid.Views.Grid;
using DevExpress.XtraGrid.Columns;
using DevExpress.XtraGrid.Views.Grid.ViewInfo;
using System.Xml;
using ProgNetComponentsDx.Options;

namespace SqlCeManager
{
    public partial class TableDefinitionForm : XtraForm
    {
        public event EventHandler<NewTabSavedEventArgs> NewTabSaved;

        public TableDefinitionForm()
        {
            InitializeComponent();
        }

        private Dictionary<string, RepositoryItemComboBox> repositories = new Dictionary<string, RepositoryItemComboBox>();
        private ConnectionNode ConnectionNode;
        private ScriptTools ScriptTools = null;
        public TableDefinitionForm(string tableName, ConnectionNode node)
            : this()
        {
            if (tableName.IsNull())
                modeProvider1.CurrentMode = ProgNetComponentsDx.Enums.FormMode.New;
            else
                modeProvider1.CurrentMode = ProgNetComponentsDx.Enums.FormMode.Edit;

            this.ConnectionNode = node;

            if (tableName.IsNull())
            {
                this.schemaDataSet1.TABLES.Rows.Add("");
                editTABLE_NAME.Select();
                editTABLE_NAME.Focus();
                editTABLE_NAME.SelectAll();
            }
            else
            {
                LoadTableData(tableName);
            }
            editTABLE_NAME.Properties.LockEvents();
            editTABLE_NAME.EditValue = tableName;
            editTABLE_NAME.Properties.UnLockEvents();
            this.Text = "[" + tableName + "]";

            this.schemaDataSet1.EnforceConstraints = true;
            ScriptTools = new ScriptTools(schemaDataSet1);
            PrepareCurrentColumn();
        }
        private void LoadTableData(string tableName)
        {
            schemaDataSet1.Clear();
            schemaDataSet1.AcceptChanges();

            SchemaDataSet dsTmp = new SchemaDataSet();
            dsTmp.EnforceConstraints = false;
            dsTmp.Merge(ConnectionNode.SchemaDataSet);
            dsTmp.TABLES.DeleteRows("TABLE_NAME <> " + tableName.EscapeSql());
            dsTmp.EnforceConstraints = true;
            dsTmp.AcceptChanges();
            this.schemaDataSet1.Merge(dsTmp);
            this.schemaDataSet1.AcceptChanges();

            string prefix = "";
            if (schemaDataSet1.COLUMNS.Rows.Count > 0)
                prefix = schemaDataSet1.COLUMNS[0].COLUMN_NAME;
            foreach (DataRow r in schemaDataSet1.COLUMNS.Rows)
            {
                if (prefix.IsNull())
                    break;
                prefix = prefix.GetCommonPrefix(r["COLUMN_NAME"].AsString());
            }
            tePrefix.Text = prefix;
        }

        private void editTABLE_NAME_EditValueChanged(object sender, EventArgs e)
        {
            this.Text = "[" + TableName + "]";
            schemaDataSet1.TABLES[0].TABLE_NAME = TableName;
        }

        private void PrepareCurrentColumn()
        {
            RefreshItems(repItemCurrentColumns, "COLUMN_NAME", schemaDataSet1.COLUMNS);
            RefreshItems(repItemCurrentColumns1, "COLUMN_NAME", schemaDataSet1.COLUMNS);
        }
        private string TableName
        {
            get { return editTABLE_NAME.EditValue.AsString().ToUpper().Trim(); }
        }
        private void gvRefColumns_CustomRowCellEdit(object sender, DevExpress.XtraGrid.Views.Grid.CustomRowCellEditEventArgs e)
        {
            int source = (sender as GridView).SourceRowHandle;
            DataRow Source = gvREFERENTIAL_CONSTRAINTS.GetDataRow(source);
            if (e.Column.FieldName == colPRIMARY_COLUMN.FieldName)
            {
                if (Source != null)
                {
                    string table = Source["UNIQUE_CONSTRAINT_TABLE_NAME"].AsString().ToUpper();
                    e.RepositoryItem = GetColumnsForTable(table);
                }
            }
        }

        private RepositoryItemComboBox GetColumnsForTable(string table)
        {
            if (table == TableName)
            {
                return repItemCurrentColumns;
            }
            else
            {
                if (repositories.ContainsKey(table))
                {
                    RefreshItems(repositories[table], "COLUMN_NAME", ConnectionNode.SchemaDataSet.COLUMNS.SelectToTable("TABLE_NAME = " + table.EscapeSql()));
                    return repositories[table];
                }
                else
                {
                    RepositoryItemComboBox rep = new RepositoryItemComboBox();
                    rep.CharacterCasing = CharacterCasing.Upper;
                    RefreshItems(rep, "COLUMN_NAME", ConnectionNode.SchemaDataSet.COLUMNS.SelectToTable("TABLE_NAME = " + table.EscapeSql()));
                    repositories.Add(table, rep);
                    return rep;
                }
            }
        }

        private void RefreshItems(RepositoryItemComboBox rep, string column, DataTable dt)
        {
            DataRow[] allRows = dt.Select(null, null, DataViewRowState.CurrentRows);
            string col = "";
            DataRow row = null;
            List<string> delCols = new List<string>();
            foreach (object o in rep.Items)
            {
                row = dt.SelectFirstRow(column + " = " + o.AsString().EscapeSql());
                if (row == null)
                    delCols.Add(o.AsString());
            }
            foreach (string s in delCols)
            {
                if (rep.Items.Contains(s))
                    rep.Items.Remove(s);
            }
            foreach (DataRow r in allRows)
            {
                col = r[column].AsString().ToUpper();

                if (!rep.Items.Contains(col))
                    rep.Items.Add(col);
            }
        }

        private void gcCOLUMNS_Leave(object sender, EventArgs e)
        {
            PrepareCurrentColumn();
        }

        protected override void OnActivated(EventArgs e)
        {
            base.OnActivated(e);
            RefreshItems(repItemTables, "TABLE_NAME", ConnectionNode.SchemaDataSet.TABLES);
        }

        private void bbiSave_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            bool result = true;
            try
            {
                this.FinishEditBindings();
                List<string> list = ScriptTools.GetScripts();
                ConnectionNode.ExecuteCommands(list);

                if (modeProvider1.CurrentMode == ProgNetComponentsDx.Enums.FormMode.New)
                {
                    RefreshItems(repItemTables, "TABLE_NAME", ConnectionNode.SchemaDataSet.TABLES);
                    if (NewTabSaved != null)
                    {
                        NewTabSaved(this, new NewTabSavedEventArgs(TableName, ConnectionNode));
                    }
                }
            }
            catch (Exception ex)
            {
                this.ShowDxErrorDialog("Query:" + ConnectionNode.LastQuery + "\nException:" + ex.Message);
                result = false;
            }
            finally
            {
                if (result)
                {
                    schemaDataSet1.AcceptChanges();
                    modeProvider1.CurrentMode = ProgNetComponentsDx.Enums.FormMode.Read;

                    ConnectionNode.GetTable(TableName);
                    LoadTableData(TableName);
                }
            }
        }

        private void bbiEdit_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            modeProvider1.CurrentMode = ProgNetComponentsDx.Enums.FormMode.Edit;
            this.schemaDataSet1.TABLES.Rows[0].BeginEdit();
        }

        private void gvCOLUMNS_InitNewRow(object sender, DevExpress.XtraGrid.Views.Grid.InitNewRowEventArgs e)
        {
            DataRow r = (sender as GridView).GetDataRow(e.RowHandle);
            if (r.Table.Columns.Contains("TABLE_NAME"))
                r["TABLE_NAME"] = TableName;
            if (r.Table.Columns.Contains("CONSTRAINT_TABLE_NAME"))
                r["CONSTRAINT_TABLE_NAME"] = TableName;
            if (r.Table.Columns.Contains("DATA_TYPE") && r["DATA_TYPE"].IsNull())
            {
                r["DATA_TYPE"] = OptionCache.GetOptionStringValue(Init.InitFormOptionNames.Main_Group.Tabele.DomyslnyTyp);
                SetDefSize(r, r["DATA_TYPE"].AsString());
            }
        }
        private void SetDefSize(DataRow row, string type)
        {
            switch (type)
            {
                case "numeric":
                    row.SetColVal("NUMERIC_PRECISION", OptionCache.GetOptionValue(Init.InitFormOptionNames.Main_Group.Tabele.NumericPrecyzja).AsInt(18));
                    row.SetColVal("NUMERIC_SCALE", OptionCache.GetOptionValue(Init.InitFormOptionNames.Main_Group.Tabele.NumericSkala).AsInt(2));
                    row.SetColVal("CHARACTER_MAXIMUM_LENGTH", DBNull.Value);
                    break;
                case "nvarchar":
                    row.SetColVal("CHARACTER_MAXIMUM_LENGTH", OptionCache.GetOptionValue(Init.InitFormOptionNames.Main_Group.Tabele.NvarcharIloscZnakow).AsInt(50));
                    row.SetColVal("NUMERIC_PRECISION", DBNull.Value);
                    row.SetColVal("NUMERIC_SCALE", DBNull.Value);
                    break;
                default:
                    row.SetColVal("CHARACTER_MAXIMUM_LENGTH", DBNull.Value);
                    row.SetColVal("NUMERIC_PRECISION", DBNull.Value);
                    row.SetColVal("NUMERIC_SCALE", DBNull.Value);
                    break;
            }
        }
        private void modeProvider1_CustomGetModes(object sender, ProgNetComponentsDx.Mode.CustomModeSettingsEventArgs e)
        {
            if (sender == bbiSetPkWithAutoInc)
            {
                bool readOnly = true;
                DataRow[] rows = gvCOLUMNS.GetSelectedDataRows();
                if (rows.Length == 1)
                {
                    if (rows[0]["DATA_TYPE"].AsString() == "int")
                        readOnly = false;
                }
                e.IsReadOnly = readOnly;
            }
            if (sender is GridColumn)
            {
                GridColumn col = sender as GridColumn;

                if (e.Row == null)
                {
                    if (gvCOLUMNS.Columns.Contains(col) ||
                        gvINDEXES.Columns.Contains(col) ||
                        gvREFERENTIAL_CONSTRAINTS.Columns.Contains(col))
                    {
                        if (col == colCOLUMN_NAME ||
                            col == colINDEX_NAME ||
                            col == colCONSTRAINT_NAME)
                        {

                        }
                        else
                        {
                            e.IsReadOnly = true;
                        }
                    }
                }
                else
                {
                    if (col == colAUTOINC_INCREMENT ||
                    col == colAUTOINC_SEED)
                    {
                        if (e.Row["DATA_TYPE"].AsString() != "int")
                            e.IsReadOnly = true;
                    }
                    if (col == colCHARACTER_MAXIMUM_LENGTH)
                    {
                        if (!ScriptTools.CanSetMaxLength(e.Row["DATA_TYPE"].AsString()))
                            e.IsReadOnly = true;
                        else
                            e.IsRequired = true;
                    }
                    if (col == colNUMERIC_PRECISION || col == colNUMERIC_SCALE)
                    {
                        if (e.Row["DATA_TYPE"].AsString() != "numeric")
                            e.IsReadOnly = true;
                        if (e.Row["DATA_TYPE"].AsString() == "numeric")
                            e.IsRequired = true;
                    }
                    if (col == colUNIQUE)
                    {
                        if (e.Row["PRIMARY_KEY"].AsBool())
                            e.IsReadOnly = true;
                    }
                    if (col.Name == colPRIMARY_COLUMN.Name)
                    {
                        string name = e.Row["CONSTRAINT_NAME"].AsString();
                        DataRow refRow = schemaDataSet1.REFERENTIAL_CONSTRAINTS.SelectFirstRow("CONSTRAINT_NAME = " + name.EscapeSql());
                        if (refRow != null)
                        {
                            if (refRow["UNIQUE_CONSTRAINT_TABLE_NAME"].IsNull())
                            {
                                e.IsReadOnly = true;
                            }
                        }
                    }
                }
            }
        }

        private void gvINDEXES_CellValueChanging(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)
        {
            if (e.Column == colPRIMARY_KEY && e.Value.AsBool())
            {
                gvINDEXES.SetRowCellValue(e.RowHandle, colUNIQUE, true);
            }
        }

        private void AddIndex(DataRow[] columns, bool pk, bool uk)
        {
            SchemaDataSet.INDEXESRow newPk = schemaDataSet1.INDEXES.NewINDEXESRow();
            string nameOrg = TableName + (pk ? "_PK" : (uk ? "_UK" : "_IDX"));
            int i = 1;
            string name = nameOrg;
            while (schemaDataSet1.INDEXES.SelectFirstRow("INDEX_NAME = " + name.EscapeSql()) != null)
            {
                name = nameOrg + i;
                i++;
            }
            newPk.TABLE_NAME = TableName;
            newPk.INDEX_NAME = name;
            newPk.UNIQUE = pk | uk;
            newPk.PRIMARY_KEY = pk;
            schemaDataSet1.INDEXES.AddINDEXESRow(newPk);

            foreach (DataRow c in columns)
            {
                SchemaDataSet.INDEXES_COLUMNSRow col = schemaDataSet1.INDEXES_COLUMNS.NewINDEXES_COLUMNSRow();
                col.INDEX_NAME = name;
                col.COLUMN_NAME = c["COLUMN_NAME"].AsString();
                schemaDataSet1.INDEXES_COLUMNS.AddINDEXES_COLUMNSRow(col);
                if (pk || uk)
                {
                    c["IS_NULLABLE"] = "NO";
                }
            }
            gcINDEXES.RefreshDataSource();
        }

        private void biSetPK_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            DataRow[] rows = gvCOLUMNS.GetSelectedDataRows();
            if (rows != null && rows.Length > 0)
            {
                bool create =true;
                DataRow current = schemaDataSet1.INDEXES.SelectFirstRow("PRIMARY_KEY = 1", null, DataViewRowState.CurrentRows);
                if (current != null)
                {
                    if (this.ShowDxLngYNQuestionDialog("REPLACE_PK") == DialogResult.Yes)
                    {
                        current["PRIMARY_KEY"] = false;
                    }
                    else
                        create = false;
                }
                if (create)
                {
                    AddIndex(rows, true, true);
                }
            }
        }
        private void bbiSetUK_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            DataRow[] rows = gvCOLUMNS.GetSelectedDataRows();
            if (rows != null && rows.Length > 0)
            {
                AddIndex(rows, false, true);
            }
        }
        private void bbiSetIdx_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            DataRow[] rows = gvCOLUMNS.GetSelectedDataRows();
            if (rows != null && rows.Length > 0)
            {
                AddIndex(rows, false, false);
            }
        }
        private void bbiSetPkWithAutoInc_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            DataRow[] rows = gvCOLUMNS.GetSelectedDataRows();
            if (rows != null && rows.Length == 1)
            {
                AddIndex(rows, true, true);
                rows[0]["AUTOINC_SEED"] = 1;
                rows[0]["AUTOINC_INCREMENT"] = 1;
            }
        }
        private void gvCOLUMNS_MouseDown(object sender, MouseEventArgs e)
        {
            if (modeProvider1.CurrentMode != ProgNetComponentsDx.Enums.FormMode.Read
                && modeProvider1.CurrentMode != ProgNetComponentsDx.Enums.FormMode.View)
            {
                if (e.Button == MouseButtons.Right)
                {
                    GridHitInfo ghi = gvCOLUMNS.CalcHitInfo(e.Location);
                    if (ghi.HitTest == GridHitTest.RowIndicator)
                    {
                        gvCOLUMNS.CloseEditor();
                        gvCOLUMNS.UpdateCurrentRow();
                        int[] idx = gvCOLUMNS.GetSelectedRows();
                        if (idx.Length == 1)
                        {
                            gvCOLUMNS.FocusedRowHandle = ghi.RowHandle;
                            gvCOLUMNS.UnselectRow(idx[0]);
                            gvCOLUMNS.SelectRow(ghi.RowHandle);
                        }
                        modeProvider1.RefreshObject(bbiSetPkWithAutoInc);
                        popupMenu1.ShowPopup(Control.MousePosition);
                    }
                }
            }
        }

        private void bbiSetFK_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            DataRow[] rows = gvCOLUMNS.GetSelectedDataRows();
            if (rows != null && rows.Length > 0)
            {
                AddFk(rows);
            }
        }

        private void AddFk(DataRow[] rows)
        {
            if (rows.Length > 0)
            {
                string name = rows[0]["COLUMN_NAME"].AsString() + "_FK";

                SchemaDataSet.REFERENTIAL_CONSTRAINTSRow r = schemaDataSet1.REFERENTIAL_CONSTRAINTS.NewREFERENTIAL_CONSTRAINTSRow();
                r.CONSTRAINT_NAME = name;
                r.CONSTRAINT_TABLE_NAME = TableName;
                r.DELETE_RULE = "NO ACTION";
                r.UPDATE_RULE = "NO ACTION";
                schemaDataSet1.REFERENTIAL_CONSTRAINTS.AddREFERENTIAL_CONSTRAINTSRow(r);
                foreach (DataRow row in rows)
                {
                    SchemaDataSet.REFERENTIAL_CONSTRAINTS_COLUMNSRow col = schemaDataSet1.REFERENTIAL_CONSTRAINTS_COLUMNS.NewREFERENTIAL_CONSTRAINTS_COLUMNSRow();
                    col.CONSTRAINT_NAME = name;
                    col.FOREIGN_COLUMN = row["COLUMN_NAME"].AsString();
                    schemaDataSet1.REFERENTIAL_CONSTRAINTS_COLUMNS.AddREFERENTIAL_CONSTRAINTS_COLUMNSRow(col);
                }
            }
        }

        private void repositoryItemTextEdit1_EditValueChanged(object sender, EventArgs e)
        {
            BaseEdit edit = sender as BaseEdit;
            if (edit != null)
            {
                if (tePrefix.EditValue.IsNotNull())
                {
                    if (!edit.EditValue.AsString().StartsWith(tePrefix.EditValue.AsString()))
                        edit.EditValue = tePrefix.EditValue.AsString() + edit.EditValue.AsString();
                }
            }
        }

        private void gvCOLUMNS_CellValueChanged(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)
        {
            if (e.Column == colDATA_TYPE)
            {
                if (e.Value.AsString() == "numeric")
                {
                    gvCOLUMNS.SetRowCellValue(e.RowHandle, colNUMERIC_PRECISION, 18);
                    gvCOLUMNS.SetRowCellValue(e.RowHandle, colNUMERIC_SCALE, 2);
                }
            }
        }

        private void bbiKopiujXml_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            try
            {
                ScriptTools.GetXmlDef(TableName, this.schemaDataSet1, true);
                this.ShowDxInfoDialog("Schema zapisana w schowku.");
            }
            catch (Exception ex)
            {
                ex.Log();
                this.ShowDxErrorDialog("Błąd kopiowania.");
            }
        }

        private void bbiLayoutSave_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            layoutProvider1.SaveLayout();
        }

        private void bbiLayoutResetuj_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            layoutProvider1.RestoreDefaultLayout();
        }

        private void bbiLayoutWczytaj_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            layoutProvider1.ApplySavedLayout();
        }

        private void gvCOLUMNS_Settings_MenuShowing(object sender, ProgNetComponentsDx.Layout.MenuMouseEventArgs e)
        {
            GridHitInfo ghi = gvCOLUMNS.CalcHitInfo(e.Location);
            if (ghi.HitTest == GridHitTest.RowIndicator)
            {
                e.CancelShowing = true;
            }
        }

        private void repItemTables_EditValueChanged(object sender, EventArgs e)
        {
            DataRow r = gvREFERENTIAL_CONSTRAINTS.GetFocusedDataRow();
            string table = (sender as BaseEdit).EditValue.AsString();
            if (r != null)
            {
                DataRow[] rows  = r.GetChildRows("FK_REFERENTIAL_CONSTRAINTS_REFERENTIAL_CONSTRAINTS_COLUMNS");
                
                if (rows.Length == 1)
                {
                    string fkCol = rows[0]["FOREIGN_COLUMN"].AsString();
                    DataRow fkColRow = schemaDataSet1.COLUMNS.SelectFirstRow("COLUMN_NAME = " + fkCol.EscapeSql());
                    if (fkColRow != null)
                    {
                        DataRow[] pks = ConnectionNode.SchemaDataSet.INDEXES.Select("TABLE_NAME = " + table.EscapeSql());
                        foreach (DataRow pk in pks)
                        {
                            DataRow[] cols =  pk.GetChildRows("FK_INDEXES_INDEXES_COLUMNS");
                            if (cols.Length == 1)
                            {
                                DataRow column = ConnectionNode.SchemaDataSet.COLUMNS.SelectFirstRow("TABLE_NAME = " + table.EscapeSql() + " AND COLUMN_NAME = " + cols[0]["COLUMN_NAME"].AsString().EscapeSql());

                                if (column != null && column["DATA_TYPE"].AsString() == fkColRow["DATA_TYPE"].AsString())
                                {
                                    rows[0]["PRIMARY_COLUMN"] = column["COLUMN_NAME"];
                                    return;
                                }
                            }
                        }
                    }
                }
            }
        }

        private void rep_DATA_TYPE_EditValueChanged(object sender, EventArgs e)
        {
            DataRow r = gvCOLUMNS.GetFocusedDataRow();
            if (r.CanRead())
                SetDefSize(r, (sender as BaseEdit).EditValue.AsString());
        }
    }

    public class NewTabSavedEventArgs : EventArgs
    {
        private string _TableName;
        private ConnectionNode _Node;

        public string TableName
        {
            get { return _TableName; }
        }
        public ConnectionNode Node
        {
            get { return _Node; }
        }
        public NewTabSavedEventArgs(string TableName, ConnectionNode node)
        {
            _Node = node;
            _TableName = TableName;
        }

    }
}

